package util;

/**
 * @author Xiahu
 * @create 2023/8/3 0003
 */
public class SQLConstant2 {
    public static String notProcessAuthorNameQuery = "select \n" +
            "\ta.author_name\n" +
            "\t,b.query_name\n" +
            "from (\n" +
            "\tselect distinct author_name from hive.bigdata.patent_author_name\n" +
            ") a left join (\n" +
            "\tselect distinct author_name,query_name from hive.bigdata.paper_author_name\n" +
            ") b on a.author_name = b.author_name\n" +
            "left join hive.bigdata.name_disambiguate c on b.query_name = c.query_name\n" +
            "where b.author_name is not null and c.query_name is null";


    public static String deletePaperTmp = "delete from hive.bigdata.paper_author_name_tmp_%s";
    public static String deletePatentTmp = "delete from hive.bigdata.patent_author_name_tmp_%s";

    public static String queryForPaperTmp = "insert into hive.bigdata.paper_author_name_tmp_%s  \n" +
            "select * from hive.bigdata.paper_author_name where author_name = '%s'";

    public static String queryForPatentTmp = "insert into hive.bigdata.patent_author_name_tmp_%s " +
            "select * from hive.bigdata.patent_author_name where author_name = '%s'";

    public static String accessInterfaceSQL = "insert into hive.bigdata.name_disambiguate\n" +
            "select \n" +
            "\t a.query_name as query_name,\n" +
            "\t disambiguation(a.query_name,json_format(a.data),json_format(b.data)) as data\n" +
            "from (\n" +
            "\tselect \n" +
            "\t\t'1' as flag,\n" +
            "\t\tquery_name,\n" +
            "\t    cast(MAP_FROM_ENTRIES(array[('papers',cast(array_agg(distinct data) as json))]) as json) as data\n" +
            "\tfrom (\n" +
            "\t\tselect \n" +
            "\t\t\ta.query_name,\n" +
            "\t\t\tcast(MAP_FROM_ENTRIES(array[\n" +
            "\t\t\t\t('paper_id',cast(a.eid as json)),\n" +
            "\t\t\t\t('title',cast(a.title[1] as json)),\n" +
            "\t\t\t\t('year',cast(a.pubYear[1] as json)),\n" +
            "\t\t\t\t('abstract',cast(a.abstracts[1] as json)),\n" +
            "\t\t\t\t('keyword',cast(a.keyword as json)),\n" +
            "\t\t\t\t('authors',a.authors)]) as json) as data\n" +
            "\t\tfrom(\n" +
            "\t\t\tselect \n" +
            "\t\t\t\ta.query_name\n" +
            "\t\t\t\t,b.eid\n" +
            "\t\t\t\t,b.title\n" +
            "\t\t\t\t,b.pubYear\n" +
            "\t\t\t\t,b.authors\n" +
            "\t\t\t\t,b.abstracts\n" +
            "\t\t\t\t,b.keyword\n" +
            "\t\t\tfrom hive.bigdata.paper_author_name_tmp_%s a \n" +
            "\t\t\tleft join (\n" +
            "\t\t\t\tselect\n" +
            "\t\t\t\t\ta.eid\n" +
            "\t\t\t\t\t,array_agg(distinct b.text) as title\n" +
            "\t\t\t\t\t,array_agg(distinct replace(cast(json_extract(publication,'$.pubYear') as varchar) ,'\"',''))  as pubYear\n" +
            "\t\t\t\t\t,cast(array_agg(distinct cast(MAP_FROM_ENTRIES(array[\n" +
            "\t\t\t\t\t\t('Family Name',cast(c.family_name as json)),\n" +
            "\t\t\t\t\t\t('Middle Name',cast('' as json)),\n" +
            "\t\t\t\t\t\t('Given Name',cast(c.given_name as json)),\n" +
            "\t\t\t\t\t\t('email',cast('' as json)),\n" +
            "\t\t\t\t\t\t('org',cast(array[c.org] as json))\n" +
            "\t\t\t\t\t\t]) as json)) as json) as authors\n" +
            "\t\t\t\t\t,array_agg(distinct d.text) as abstracts\n" +
            "\t\t\t\t\t,'' as keyword\n" +
            "\t\t\t\tfrom hive.bigdata.paper_author_name_tmp_%s a\n" +
            "\t\t\t\tleft join (\n" +
            "\t\t\t\t\tselect  \n" +
            "\t\t\t\t\t\t eid\n" +
            "\t\t\t\t\t\t,x.text\n" +
            "\t\t\t\t\tfrom hive.bigdata.paper_author_name_tmp_%s a\n" +
            "\t\t\t\t\tcross join unnest (cast(json_extract(a.titles,'$') as array(row(text varchar)))) as x(text)\n" +
            "\t\t\t\t\twhere x.text is not null\n" +
            "\t\t\t\t) b on a.eid = b.eid\n" +
            "\t\t\t\tleft join (\n" +
            "\t\t\t\t\tselect  \n" +
            "\t\t\t\t\t\t eid\n" +
            "\t\t\t\t\t\t ,y.auid as author_id\n" +
            "\t\t\t\t\t\t ,case when y.auid is null then '' else y.auid end\tas author_id \n" +
            "\t\t\t\t\t\t ,case when y.lastName is null then '' else y.lastName end\tas family_name \n" +
            "\t\t\t\t\t\t ,case when y.firstName is null then '' else y.firstName end as given_name\n" +
            "\t\t\t\t\t\t ,case when json_format(json_array_get(json_extract(x.affiliation,'$.organizations') ,json_array_length(json_extract(x.affiliation,'$.organizations'))-1)) is null then '' else json_format(json_array_get(json_extract(x.affiliation,'$.organizations') ,json_array_length(json_extract(x.affiliation,'$.organizations'))-1)) end  as org\n" +
            "\t\t\t\t\tfrom hive.bigdata.paper_author_name_tmp_%s a\n" +
            "\t\t\t\t\tcross join unnest (cast(json_extract(a.authorGroups,'$') as array(row(affiliation json,authors json)))) as x(affiliation,authors) \n" +
            "\t\t\t\t\tcross join unnest (cast(json_extract(x.authors,'$') as array(row(auid varchar,lastName varchar,firstName varchar)))) as y(auid,lastName,firstName)\n" +
            "\t\t\t\t)c on  a.eid = c.eid\n" +
            "\t\t\t\tleft join (\n" +
            "\t\t\t\t\tselect  \n" +
            "\t\t\t\t\t\t eid\n" +
            "\t\t\t\t\t\t,x.text\n" +
            "\t\t\t\t\tfrom hive.bigdata.paper_author_name_tmp_%s a\n" +
            "\t\t\t\t\tcross join unnest (cast(json_extract(a.abstracts,'$') as array(row(text varchar)))) as x(text)\n" +
            "\t\t\t\t\twhere x.text is not null\n" +
            "\t\t\t\t) d on a.eid = d.eid  where d.text is not null and b.text is not null\n" +
            "\t\t\t\tgroup by a.eid\n" +
            "\t\t\t) b on a.eid = b.eid where b.eid is not null\n" +
            "\t\t) a \n" +
            "\t) group by query_name\t\n" +
            ") a left join (\n" +
            "\tselect \n" +
            "\t\t'1' as flag,\n" +
            "\t    cast(MAP_FROM_ENTRIES(array[('patents',cast(array_agg(distinct data) as json))]) as json) as data\n" +
            "\tfrom (\n" +
            "\t\tselect \n" +
            "\t\t\ta.author_name,\n" +
            "\t\t\tcast(MAP_FROM_ENTRIES(array[\n" +
            "\t\t\t\t('patent_id',cast(a.apply_no as json)),\n" +
            "\t\t\t\t('inventors',cast(a.inventors as json)),\n" +
            "\t\t\t\t('right_holder',cast(a.right_holder as json)),\n" +
            "\t\t\t\t('title',cast(a.title[1] as json)),\n" +
            "\t\t\t\t('abstract',cast(a.abstract[1] as json))]) as json) as data\n" +
            "\t\tfrom(\n" +
            "\t\t\tselect \n" +
            "\t\t\t\ta.author_name\n" +
            "\t\t\t\t,b.apply_no\n" +
            "\t\t\t\t,b.inventors\n" +
            "\t\t\t\t,b.right_holder\n" +
            "\t\t\t\t,b.title\n" +
            "\t\t\t\t,b.abstract\n" +
            "\t\t\tfrom hive.bigdata.patent_author_name_tmp_%s a \n" +
            "\t\t\tleft join (\n" +
            "\t\t\t\tselect\n" +
            "\t\t\t\t\t a.applicationnumber as apply_no\n" +
            "\t\t\t\t\t,array_agg(distinct b.name) as inventors\n" +
            "\t\t\t\t\t,array_agg(distinct c.name) as right_holder\n" +
            "\t\t\t\t\t,array_agg(replace(cast(json_extract(a.title,'$[0].text') as varchar),'\"','')) as title\n" +
            "\t\t\t\t\t,array_agg(replace(cast(json_extract(json_extract(a.abstracts,'$[0].text'),'$[0]') as varchar),'\"','')) as abstract\n" +
            "\t\t\t\tfrom (select * from hive.bigdata.patent_author_name_tmp_%s where length(title) > 2 and length(abstracts) > 2) a\n" +
            "\t\t\t\tleft join (\n" +
            "\t\t\t\t\tselect  \n" +
            "\t\t\t\t\t\ta.applicationnumber,\n" +
            "\t\t\t\t\t\tx.name as name \n" +
            "\t\t\t\t\tfrom hive.bigdata.patent_author_name_tmp_%s a\n" +
            "\t\t\t\t\tcross join unnest (cast(json_extract(a.inventors,'$') as array(row(name varchar)))) as x(name)\n" +
            "\t\t\t\t\twhere x.name is not null\n" +
            "\t\t\t\t) b on a.applicationnumber = b.applicationnumber\n" +
            "\t\t\t\tleft join (\n" +
            "\t\t\t\t\tselect  \n" +
            "\t\t\t\t\t\ta.applicationnumber,\n" +
            "\t\t\t\t\t\tx.name as name \n" +
            "\t\t\t\t\tfrom hive.bigdata.patent_author_name_tmp_%s a\n" +
            "\t\t\t\t\tcross join unnest (cast(json_extract(a.applicants,'$') as array(row(name varchar)))) as x(name)\n" +
            "\t\t\t\t\twhere x.name is not null\n" +
            "\t\t\t\t)c on  a.applicationnumber = c.applicationnumber\n" +
            "\t\t\t\tgroup by a.applicationnumber\n" +
            "\t\t\t)b on a.applicationnumber = b.apply_no where b.apply_no is not null\n" +
            "\t\t) a \n" +
            "\t) group by author_name\n" +
            ") b on a.flag = b.flag";
}
